package com.ajaaxx.db.operations;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.ajaaxx.datatypes.Question;
import com.ajaaxx.datatypes.User;
import com.ajaaxx.datatypes.Subject;
import com.ajaaxx.util.Util;

public class DBOperations {
	
	private final String url="jdbc:mysql://localhost/test";
	private final String username="root";
	private final String password="root";
	
	private static final Set<Question> questions = new HashSet<Question> ();
	
	private Connection getConnection(){
		Connection conn = null;
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance ();		
			conn = DriverManager.getConnection (url, username, password);
		}
		catch(Exception e){e.printStackTrace();}
		
		return conn;
	}

	
	public User getUserBasedOnCedulla(int cedulla){
		User user = null;
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet result = null;
		try {
			
			conn = getConnection();
			ps = getConnection().prepareStatement(Queries.FETCH_USER_DETAILS_QUERY);
			ps.setInt(1, cedulla);
			
			result = ps.executeQuery();
			
			while(result.next()){
				user = new User();
				user.setCedulla(cedulla);
				user.setCurrentSemester(result.getString("CURRENT_SEMESTER"));
				user.setDepartment(result.getString("DEPARTMENT"));
				user.setUserName(result.getString("NAME"));
				user.setUserType(Util.mapUserType(result.getString("USER_TYPE")));
				user.setSurveyComplete(result.getInt("SURVEY_STATUS"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				if(result!=null && !result.isClosed()){result.close();}
				if(ps!=null && !ps.isClosed()){ps.close();}
				if(conn!=null && !conn.isClosed()){conn.close();}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return user;
	}
	
	
	public Set<Question> getQuestions(){

		if(questions.size()>0) return questions;
		
		Connection conn=null;
		Statement st=null;
		ResultSet result = null;
		try {
			
			conn = getConnection();
			st = getConnection().createStatement();			
			result = st.executeQuery(Queries.FETCH_QUESTIONS);
			
			while(result.next()){
				
				Question q = new Question();
				
				q.setQuestionId(result.getInt("QUESTION_ID"));
				q.setQuestionString(result.getString("QUESTION"));
				q.setSubjectId(result.getInt("SUBJECT_ID"));
				q.setUserType(result.getString("USER_TYPE"));
				questions.add(q);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				if(result!=null && !result.isClosed()){result.close();}
				if(st!=null && !st.isClosed()){st.close();}
				if(conn!=null && !conn.isClosed()){conn.close();}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return questions;
		
	}
	
	
	
	public Set<Subject> getSubjects(User user){

		Set<Subject> subjects = null;
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet result = null;
		try {
			
			conn = getConnection();
			if(Util.isStudent(user.getUserType())){
				ps = getConnection().prepareStatement(Queries.FETCH_SUBJECTS_STUDENTS);
				ps.setInt(1, user.getCedulla());
				ps.setString(2, user.getCurrentSemester());
			}
			else{
				ps = getConnection().prepareStatement(Queries.FETCH_SUBJECTS_OTHERS);
				ps.setInt(1, user.getCedulla());
			}
			
			
			
			result = ps.executeQuery();
			
			subjects = new HashSet<Subject>();
			
			while(result.next()){
				
				Subject s = new Subject();
				s.setSubjectName(result.getString("NAME"));
				s.setSubjectId(result.getInt("ID"));
				
				subjects.add(s);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			try {
				if(result!=null && !result.isClosed()){result.close();}
				if(ps!=null && !ps.isClosed()){ps.close();}
				if(conn!=null && !conn.isClosed()){conn.close();}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return subjects;
		
	}
	
	
	public Map<Integer,Integer> fetchRelations(int cedulla,String currentSemester){
		Connection conn=null;
		ResultSet result = null;
		Map<Integer,Integer> relations = null;
		PreparedStatement fetchRelationsPS = null;
		
		try {
			
			conn = getConnection();
			conn.setAutoCommit(false);
			fetchRelationsPS = getConnection().prepareStatement(Queries.FETCH_RELATIONS);
			fetchRelationsPS.setInt(1, cedulla);
			fetchRelationsPS.setString(2, currentSemester);
			
			result = fetchRelationsPS.executeQuery();
			
			relations = new HashMap<Integer, Integer>();
			
			while(result.next()){
				
				
				relations.put(result.getInt("SUBJECT_ID"), result.getInt("RELATION_ENTRY"));			
			}}
			catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		finally{
			try {
				if(result!=null && !result.isClosed()){result.close();}
				if(fetchRelationsPS!=null && !fetchRelationsPS.isClosed()){fetchRelationsPS.close();}
				if(conn!=null && !conn.isClosed()){conn.close();}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		}
		
		return relations;
	}
	
	
	
	public boolean submitSurveyResult(User user, Map<Integer,StringBuffer> processedQuestions){
		
		Connection conn=null;
		PreparedStatement updateUserStatus=null;
		PreparedStatement submitSurveyPS=null;
		ResultSet result = null;
		
		try {
			
			conn = getConnection();
			conn.setAutoCommit(false);
			
			submitSurveyPS = conn.prepareStatement(Queries.SUBMIT_SURVEY);
			
			Set<Map.Entry<Integer,StringBuffer>> entrySet = processedQuestions.entrySet();
			
			
			for(Map.Entry<Integer,StringBuffer> entry:entrySet){
				submitSurveyPS.setInt(1,entry.getKey());
				submitSurveyPS.setString(2,entry.getValue().toString());
				submitSurveyPS.addBatch();
			}
			
			
			int[] updatedResult = submitSurveyPS.executeBatch();
			
			if(updatedResult.length!=processedQuestions.size()) {conn.rollback();return false;}
			
			
			updateUserStatus = conn.prepareStatement(Queries.UPDATE_USER_STATUS);
			updateUserStatus.setInt(1, user.getCedulla());
			
			int countUpdated = updateUserStatus.executeUpdate();
			
			if(!(countUpdated>0)){conn.rollback();return false;}
			
			conn.commit();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		finally{
			try {
				if(result!=null && !result.isClosed()){result.close();}
				if(updateUserStatus!=null && !updateUserStatus.isClosed()){updateUserStatus.close();}
				if(submitSurveyPS!=null && !submitSurveyPS.isClosed()){submitSurveyPS.close();}
				if(conn!=null && !conn.isClosed()){conn.close();}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
		}
		
		return true;
		
	}
}
